CREATE DATABASE Train
GO

USE Train
GO

CREATE TABLE Contact
(
	ID int IDENTITY (1,1) PRIMARY KEY,
	Name varchar(500),
	[Address] varchar(500),
	Phone varchar(20),
	Email varchar(500),
	Detail varchar(max),
	[Date] date
)
GO

CREATE TABLE [Route]
(
	Route_Name varchar(500) PRIMARY KEY,
	Depature varchar(500),
	Destination varchar(500),
	Distance int
)
GO

CREATE TABLE [Station]
(
	Station_Name varchar(500) PRIMARY KEY,
	[Address] varchar(500),
	Phone varchar(20)
)
GO

CREATE TABLE Schedule
(
	Schedule_ID int IDENTITY(1,1) PRIMARY KEY,
	[Date] date,
	[Time] time
)
GO

CREATE TABLE Train
( 
	Train_No int IDENTITY(1,1) PRIMARY KEY,
	Train_Name varchar(500),
	Station_Name varchar(500) FOREIGN KEY REFERENCES Station (Station_Name),
	Route_Name varchar(500) FOREIGN KEY REFERENCES [Route] (Route_Name),
	AC_1 int,
	Ac_2 int,
	Ac_3 int,
	Sleeper int,
	General int
)
GO

CREATE TABLE Train_Schedule
(
	Train_Schedule_ID int IDENTITY(1,1) PRIMARY KEY,
	Schedule_ID int FOREIGN KEY REFERENCES Schedule (Schedule_ID),
	Train_No int FOREIGN KEY REFERENCES Train (Train_No)
)
GO

CREATE TABLE Passenger
(
	Username varchar(100) PRIMARY KEY,
	[Password] varchar(100),
	Name varchar(500),
	Birth date,
	Gender bit,
	[Address] varchar(500),
	Phone varchar(20),
	Email varchar(500),
	Date_Of_Travel date
)
GO

CREATE TABLE [Order]
(
	Ord_ID int IDENTITY(1,1) PRIMARY KEY,
	Username varchar(100) FOREIGN KEY REFERENCES Passenger(Username),
	Total float,
	[Date] date
)
GO

CREATE TABLE OrderDetail
(
	ID int IDENTITY(1,1) PRIMARY KEY,
	Ord_ID int FOREIGN KEY REFERENCES [Order](Ord_ID),
	Train_Schedule_ID int FOREIGN KEY REFERENCES Train_Schedule (Train_Schedule_ID),
	Type_Ticket varchar(50),
	Quantity int
)
GO

CREATE TABLE Account 
(
	Username varchar(500) PRIMARY KEY,
	[Password] varchar(100),
	Name varchar(500),
	[Role] int,
	[Status] int
)
GO


-------------------------------------------------------------INSERT STATIONS
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Sai Gon','136 Ham Nghi - District 1 - HCM City','112-112-1234')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Bien Hoa','137 Ham Nghi - District 2 - BienHoa City','113-112-1235')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Long Khanh','138 Ham Nghi - District 4 - LongKhanh City','114-112-1236')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Phan Thiet','139 Ham Nghi - District 5 - PhanThiet City','115-112-1237')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Binh Thuan','140 Ham Nghi - District 6 - BinhThuan City','116-112-1238')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Ma Lam','141 Ham Nghi - District 7 - MaLam City','117-112-1239')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Song Mao','142 Ham Nghi - District 8 - SongMao City','118-112-1210')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Thap Cham','143 Ham Nghi - District 9 - ThapCham City','119-112-1211')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Nga Ba','144 Ham Nghi - District 10 - NgaBa City','120-112-1212')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Nha Trang','145 Ham Nghi - District 11 - NhaTrang City','121-112-1213')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Ninh Hoa','146 Ham Nghi - District 12 - NinhHoa City','122-112-1214')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Tay Hoa','147 Ham Nghi - District 13 - TayHoa City','123-112-1215')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Dieu Tri','148 Ham Nghi - District 14 - DieuTri City','124-112-1216')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Qui Nhon','149 Ham Nghi - District 15 - QuyNhon City','125-112-1217')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Bong Son','150 Ham Nghi - District 16 - BongSon City','126-112-1218')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Duc Pho','151 Ham Nghi - District 17 - Ducpho City','127-112-1219')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Quang Ngai','152 Ham Nghi - District 18 - QuangNgai City','128-112-1220')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Nui Thanh ','153 Ham Nghi - District 19 - NuiTHanh City','129-112-1221')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Tam ky','154 Ham Nghi - District 20 - TamKy City','130-112-1222')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Tra Kieu','155 Ham Nghi - District 21 - Tra Kieu City','131-112-1222')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Da Nang','156 Ham Nghi - District 22 - DaNang City','132-112-1223')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Lang Co','157 Ham Nghi - District 23 - LangCo City','133-112-1224')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Hue','158 Ham Nghi - District 24 - Hue City','134-112-1225')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Dong Ha','159 Ham Nghi - District 25 - DongHa City','135-112-1226')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Dong Hoi','160 Ham Nghi - District 26 - DongHoi City','136-112-1227')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Dong Que','161 Ham Nghi - District 27 - DongQue City','137-112-1228')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Huong Que','162 Ham Nghi - District 28 - HuongQue City','138-112-1229')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Yen Trung','163 Ham Nghi - District 29 - YenTrung City','139-112-1230')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Vinh','164 Ham Nghi - District 30 - Vinh City','140-112-1231')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Cho Sy','165 Ham Nghi - District 31 - ChoSy City','141-112-1232')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Minh Khoi','166 Ham Nghi - District 32 - MinhKhoi City','142-112-1233')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Thanh Hoa','167 Ham Nghi - District 33 - ThanhHoa City','143-112-1235')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Bim Son','168 Ham Nghi - District 34 - BimSon City','144-112-1236')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Ninh Binh','169 Ham Nghi - District 35 - NinhBinh City','145-112-1237')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Nam Dinh','170 Ham Nghi - District 36 - NamDinh City','146-112-1238')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Phu Ly','171 Ham Nghi - District 38 - PhuLy City','147-112-1239')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Giap Bat','172 Ham Nghi - District 39 - GiapBat City','148-112-1240')
INSERT INTO Station
([Station_Name],[Address],[Phone]) VALUES ('Ha Noi','173 Ham Nghi - District 40 - HaNoi City','149-112-1241')
GO
SELECT * FROM Station


-------------------------------------------------------------INSERT STATIONS
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE1','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE2','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE3','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE4','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE5','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE6','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE7','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE8','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE9','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE10','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE11','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE12','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE13','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE14','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE15','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE16','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE17','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE18','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE19','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE20','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE21','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE22','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE23','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE24','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE25','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('SE26','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN1','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN2','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN3','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN4','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN5','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN6','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN7','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN8','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN9','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN10','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN11','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN12','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN13','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN14','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN15','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN16','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN17','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN18','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN19','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN20','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN21','','','','','','','')
INSERT INTO [Train]
(Train_Name,Station_Name,Route_Name,AC_1,Ac_2,Ac_3,Sleeper,General) VALUES ('TN22','','','','','','','')

